﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Cosmetic.Logic
{
     public  class MemberRegulationManager
    {
         static string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        /// <summary>
        /// 返回dataSet.
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
         public static DataSet GetAllDataList()
         {
             
             SqlConnection con = new SqlConnection(conStr);
             con.Open();
             string cmdStr = "SELECT [Level], SingleInvestment, Months, AccumulateInvestment, KeepTime, DiscountRate, ActivityTimeStart, ActivityTimeEnd, Balance, other  FROM  MemberRegulation ";
             SqlCommand com = new SqlCommand(cmdStr, con);
             SqlDataAdapter dataadapt = new SqlDataAdapter(com);
             DataSet ds = new DataSet();
             dataadapt.Fill(ds);
             con.Close();
             return ds;
         }

         /// <summary>
         /// 添加数据
         /// </summary>
         /// <param name="Level"></param>
         /// <param name="SingleInvestment"></param>
         /// <param name="Months"></param>
         /// <param name="AccumulateInvestment"></param>
         /// <param name="KeepTime"></param>
         /// <param name="DiscountRate"></param>
         /// <param name="ActivityTimeStart"></param>
         /// <param name="ActivityTimeEnd"></param>
         /// <param name="Balance"></param>
         /// <param name="other"></param>
         /// <returns></returns>
         public static int InsertRowData(string Level, int SingleInvestment, int Months, int AccumulateInvestment, int KeepTime, double DiscountRate, DateTime ActivityTimeStart, DateTime ActivityTimeEnd , int Balance,int other)
         {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
             SqlConnection con = new SqlConnection(conStr);
             con.Open();
             string cmdStr = "INSERT INTO MemberRegulation values( '" + Level + "', " + SingleInvestment + ", " + Months + ", " + AccumulateInvestment + ", " + KeepTime + ", " + DiscountRate + ", '" + ActivityTimeStart + "','" + ActivityTimeEnd + "', "+Balance+", "+other+" )";
             SqlCommand com = new SqlCommand(cmdStr, con);
             int a = com.ExecuteNonQuery();
             con.Close();
             return a;
         }

         /// <summary>
         /// 根据Level字段，更新数据。
         /// </summary>
         /// <param name="Level"></param>
         /// <param name="SingleInvestment"></param>
         /// <param name="Months"></param>
         /// <param name="AccumulateInvestment"></param>
         /// <param name="KeepTime"></param>
         /// <param name="DiscountRate"></param>
         /// <param name="ActivityTimeStart"></param>
         /// <param name="ActivityTimeEnd"></param>
         /// <param name="Balance"></param>
         /// <param name="other"></param>
         /// <returns></returns>
         public static int UpdateRowData(string Level, int SingleInvestment, int Months, int AccumulateInvestment, int KeepTime, double DiscountRate, DateTime ActivityTimeStart, DateTime ActivityTimeEnd ,int Balance,int other)
         {
           //  string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
             SqlConnection con = new SqlConnection(conStr);
             con.Open();
             string cmdStr = "UPDATE   MemberRegulation Set  SingleInvestment= " + SingleInvestment + ", Months=" + Months + ", AccumulateInvestment=" + AccumulateInvestment + ", KeepTime=" + KeepTime + ", DiscountRate=" + DiscountRate + ",ActivityTimeStart= '" + ActivityTimeStart + "',ActivityTimeEnd='" + ActivityTimeEnd + "' , Balance="+Balance+",  other="+other+"   where [Level]= '" + Level + "' ";
             SqlCommand com = new SqlCommand(cmdStr, con);
             int a = com.ExecuteNonQuery();
             con.Close();
             return a;
         }

         /// <summary>
         /// 根据Level来删除数据。
         /// </summary>
         /// <param name="Level"></param>
         /// <returns></returns>
         public static int DeleteRowData(string Level)
         {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
             SqlConnection con = new SqlConnection(conStr);
             con.Open();
             string cmdStr = " delete  from MemberRegulation where [Level]='"+Level+"'";
             SqlCommand com = new SqlCommand(cmdStr, con);
             int a = com.ExecuteNonQuery();
             con.Close();
             return a;
         }

         /// <summary>
         /// 用来升级会员的级别。
         /// </summary>
         /// <param name="userId"></param>
         /// <param name="singleInvestment"></param>
         public static void RaisLevelBySingleInvestment(string userId, int singleInvestment)
         {
             SqlConnection con = new SqlConnection(conStr);
             con.Open();
             string levelcmd = "SELECT  TOP (1) [Level]  FROM  MemberRegulation  WHERE  SingleInvestment <= " + singleInvestment + "  ORDER BY SingleInvestment DESC ";
             SqlCommand com = new SqlCommand(levelcmd, con);
             object availableLevel = com.ExecuteScalar(); //返回该这笔单笔消费应该是哪一级的。
             
             if (availableLevel == null)
             {
                 return; //没有级别的话就直接退出。
             }
             else
             {
                 //判断些会员是否底于这个级别，如果低的话就升到这个级别，否则保留原级别
                 string hasLevel = "SELECT  [Level] FROM [User] WHERE  (UserId = '" + userId + "')";
                 SqlCommand hascom = new SqlCommand(hasLevel, con);
                 object hasLevelobject = hascom.ExecuteScalar();
               
                 if (hasLevelobject == null)
                 {
                     string updateCmd = "UPDATE  [User] SET  [Level] = '" + availableLevel.ToString() + "'  WHERE  (UserId = '" + userId + "')";
                     SqlCommand execu = new SqlCommand(updateCmd, con);
                     execu.ExecuteNonQuery();
                 }
                 else
                 {
                     string hasLevelstr = hasLevelobject.ToString();
                     if (IsRais(hasLevelstr, availableLevel.ToString()))
                     {
                         string updatecom = "UPDATE  [User] SET  [Level] = '" + availableLevel.ToString() + "'  WHERE  (UserId = '" + userId + "')";
                         SqlCommand execuUpdate = new SqlCommand(updatecom, con);
                         execuUpdate.ExecuteNonQuery();
                     }
                 }
             }
             con.Close();
         }

         /// <summary>
         /// 判断会员原有的级别和现在可以达到的级别相比，TRUE表示可以升级。
         /// </summary>
         /// <param name="hasLevel"></param>
         /// <param name="availableLevel"></param>
         /// <returns></returns>
         public static bool IsRais(string hasLevel, string availableLevel)
         {
             SqlConnection con = new SqlConnection(conStr);
             con.Open();
             string hasLevelStr = "Select other from MemberRegulation  where [Level]='" + hasLevel + "'";
             SqlCommand hascomd=new SqlCommand(hasLevelStr,con);
             int hasInt = Convert.ToInt32(hascomd.ExecuteScalar());

             string availableLevelStr = "Select other from MemberRegulation  where [Level]='" + availableLevel + "'";
             SqlCommand availablecomd = new SqlCommand(availableLevelStr, con);
             int availableInt = Convert.ToInt32(availablecomd.ExecuteScalar());
             con.Close();
             if (hasInt >= availableInt)
             {
                 return false;
             }
             else
             {
                 return true;
             }
         }
    }//end class
}
